package com.javaEE.tes.dao;

import com.javaEE.tes.dao.dto.StudentAssociation;
import com.javaEE.tes.entities.Association;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface AssociationRepository extends CrudRepository<Association,Long> {
    @Autowired
    List<Association> findAll();
    @Query(nativeQuery = true,value = "SELECT a.association_name as associationName,a.activity,t.name1, COUNT(sa.student_id) as num " +
            "FROM association AS a  JOIN teacher AS t ON a.teacher_id=t.teacher_id LEFT JOIN student_association AS sa ON a.association_id = sa.association_id "+
            "GROUP BY a.association_name,a.activity,t.name1,num ORDER BY a.association_name;"
    )
    List<StudentAssociation> studentAssociation();
}
